Migrating from 8.3 to 8.4 on the same server

In the documentation under the heading "Migration Between Releases" we
read:

< create a slave server with the updated version of PostgreSQL. The slave
can be on the same computer or a different computer. [...] Such a
switch-over results in only several seconds of downtime for an upgrade. >>

In the section "Warm Standby Servers for High Availability" it says:

<< For testing purposes, it is possible to run both primary and standby
servers on the same system. >>

Although this section does not use the term Point-In-Time Recovery, I
understand this is what it refers to. Could the WAL/PITR method then be
used to upgrade from 8.3 to 8.4 on the same server and avoid having the
significant downtime of dump/restore?

If so, I understand the two servers would have to run on different
ports. Are there any other issues/traps to be aware of?

--

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

On Wed, Apr 14, 2010 at 01:29:23PM -0400, Lewis Kapell wrote:
> In the documentation under the heading "Migration Between Releases" we
> read:
>
> < > a slave server with the updated version of PostgreSQL. The slave can be on
> the same computer or a different computer. [...] Such a switch-over results
> in only several seconds of downtime for an upgrade. >>
>
> In the section "Warm Standby Servers for High Availability" it says:
>
> << For testing purposes, it is possible to run both primary and standby
> servers on the same system. >>
>
> Although this section does not use the term Point-In-Time Recovery, I
> understand this is what it refers to. Could the WAL/PITR method then be
> used to upgrade from 8.3 to 8.4 on the same server and avoid having the
> significant downtime of dump/restore?
>
> If so, I understand the two servers would have to run on different ports.
> Are there any other issues/traps to be aware of?
>
> --
>
> Thank you,
>
> Lewis Kapell
> Computer Operations
> Seton Home Study School
>
I believe that the server versions must be the same in PITR so
you cannot use it to upgrade.

Cheers,
Ken

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

Lewis Kapell wrote:

> Could the WAL/PITR method then be used to upgrade from 8.3 to 8.4
> on the same server and avoid having the significant downtime of
> dump/restore?

No, but you might want to check out pg_migrator.

http://pgfoundry.org/projects/pg-migrator/

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

--00c09f83a5b89df7ee04843662b7
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Apr 14, 2010 at 1:42 PM, Kenneth Marshall wrote:

> On Wed, Apr 14, 2010 at 01:29:23PM -0400, Lewis Kapell wrote:
> > In the documentation under the heading "Migration Between Releases" we
> > read:
> >
> > < > create
> > a slave server with the updated version of PostgreSQL. The slave can be
> on
> > the same computer or a different computer. [...] Such a switch-over
> results
> > in only several seconds of downtime for an upgrade. >>
> >
> > In the section "Warm Standby Servers for High Availability" it says:
> >
> > << For testing purposes, it is possible to run both primary and standby
> > servers on the same system. >>
> >
> > Although this section does not use the term Point-In-Time Recovery, I
> > understand this is what it refers to. Could the WAL/PITR method then be
> > used to upgrade from 8.3 to 8.4 on the same server and avoid having the
> > significant downtime of dump/restore?
> >
> > If so, I understand the two servers would have to run on different ports.
> > Are there any other issues/traps to be aware of?
> >
> > --
> >
> > Thank you,
> >
> > Lewis Kapell
> > Computer Operations
> > Seton Home Study School
> >
> I believe that the server versions must be the same in PITR so
> you cannot use it to upgrade.
>

Exactly, remember, one of the most important steps in PITR is the 'base'
backup. Your PITR slaves uses the same data files as its starting point as
the master. Since those aren't upgradeable (except in a few cases with
pg_migrator) you're really out. Even if you did get pg_migrator to upgrade
the base files, I wouldn't use them in a PITR setup though. I'm not 100%
sure if it changed or not, but I would assume that the WAL record format
changed from version to version.

--Scott


>
> Cheers,
> Ken
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--00c09f83a5b89df7ee04843662b7
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Re: Migrating from 8.3 to 8.4 on the same server

--=-USLgDqw5mJxj4bnEfstK
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

How big is your database?

If not very big, a pg_dump/pg_restore will be your best option. 8.3 to
8.4 is not a traumatic upgrade. In fact, it's really easy and probably
you won't need to change your database schema.
And pg_restore in 8.4 is really FAST (compared with previous versions).

-----Original Message-----
From: Lewis Kapell
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Migrating from 8.3 to 8.4 on the same server
Date: Wed, 14 Apr 2010 13:29:23 -0400


In the documentation under the heading "Migration Between Releases" we
read:

< create a slave server with the updated version of PostgreSQL. The slave
can be on the same computer or a different computer. [...] Such a
switch-over results in only several seconds of downtime for an upgrade. >>

In the section "Warm Standby Servers for High Availability" it says:

<< For testing purposes, it is possible to run both primary and standby
servers on the same system. >>

Although this section does not use the term Point-In-Time Recovery, I
understand this is what it refers to. Could the WAL/PITR method then be
used to upgrade from 8.3 to 8.4 on the same server and avoid having the
significant downtime of dump/restore?

If so, I understand the two servers would have to run on different
ports. Are there any other issues/traps to be aware of?

--

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School





--=-USLgDqw5mJxj4bnEfstK
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit








How big is your database?



If not very big, a pg_dump/pg_restore will be your best option. 8.3 to 8.4 is not a traumatic upgrade. In fact, it's really easy and probably you won't need to change your database schema.

And pg_restore in 8.4 is really FAST (compared with previous versions).



-----Original Message-----

From: Lewis Kapell <>

To:

Subject: [ADMIN] Migrating from 8.3 to 8.4 on the same server

Date: Wed, 14 Apr 2010 13:29:23 -0400

In the documentation under the heading "Migration Between Releases" we 

read:



<<It is also possible to use replication methods, such as Slony, to 

create a slave server with the updated version of PostgreSQL. The slave 

can be on the same computer or a different computer. [...] Such a 

switch-over results in only several seconds of downtime for an upgrade. >>



In the section "Warm Standby Servers for High Availability" it says:



<< For testing purposes, it is possible to run both primary and standby 

servers on the same system. >>



Although this section does not use the term Point-In-Time Recovery, I 

understand this is what it refers to.  Could the WAL/PITR method then be 

used to upgrade from 8.3 to 8.4 on the same server and avoid having the 

significant downtime of dump/restore?



If so, I understand the two servers would have to run on different 

ports.  Are there any other issues/traps to be aware of?



-- 



Thank you,



Lewis Kapell

Computer Operations

Seton Home Study School

Re: Migrating from 8.3 to 8.4 on the same server

The database is about 12gb, the dump file is about 8gb.

I tested dump/restore into 8.4 on our test server, and it took an hour.=20
This is a virtualized server, but my sysadmin thinks the performance=20
on our live server (not virtual) would be comparable.

It wouldn't kill us to have two or three hours of down time, but I would=20
like to avoid it.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



On 4/14/2010 4:28 PM, Iñigo Martinez Lasala wrote:
> How big is your database?
>
> If not very big, a pg_dump/pg_restore will be your best option. 8.3 to
> 8.4 is not a traumatic upgrade. In fact, it's really easy and probably
> you won't need to change your database schema.
> And pg_restore in 8.4 is really FAST (compared with previous versions).


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

--=-UmjfPXlFP6Orhcp42o/2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

I've never tested pg_migrator... it appears a good candidate for your
upgrade, although I think it will work over datafiles, so test it
carefully with a replica of your database.

Slony is always an option. It's not very difficult to deploy, but you
have to manually create the schema in target database and all tables
must include a primary key or an unique index. So, it's not fire and
forget. :-)

We have recently migrated our Gforge database from 8.1 to 8.4. It took
about 50 minutes for dump (20GB database into a 7.8GB dump) and 40
minutes for restore. You can speed your restore process if you restore
first your schema WITHOUT indexes, then restore data, and finally create
indexes. =20

-----Original Message-----
From: Lewis Kapell
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrating from 8.3 to 8.4 on the same server
Date: Wed, 14 Apr 2010 16:36:11 -0400


The database is about 12gb, the dump file is about 8gb.

I tested dump/restore into 8.4 on our test server, and it took an hour.=20
This is a virtualized server, but my sysadmin thinks the performance=20
on our live server (not virtual) would be comparable.

It wouldn't kill us to have two or three hours of down time, but I would=20
like to avoid it.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



On 4/14/2010 4:28 PM, Iñigo Martinez Lasala wrote:
> How big is your database?
>
> If not very big, a pg_dump/pg_restore will be your best option. 8.3 to
> 8.4 is not a traumatic upgrade. In fact, it's really easy and probably
> you won't need to change your database schema.
> And pg_restore in 8.4 is really FAST (compared with previous versions).





--=-UmjfPXlFP6Orhcp42o/2
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit








I've never tested pg_migrator... it appears a good candidate for your upgrade, although I think it will work over datafiles, so test it carefully with a replica of your database.



Slony is always an option. It's not very difficult to deploy, but you have to manually create the schema in target database and all tables must include a primary key or an unique index. So, it's not  fire and forget. :-)



We have recently migrated our Gforge database from 8.1 to 8.4. It took about 50 minutes for dump (20GB database into a 7.8GB dump) and 40 minutes for restore. You can speed your restore process if you restore first your schema WITHOUT indexes, then restore data, and finally create indexes. 



-----Original Message-----

From: Lewis Kapell <>

To:

Subject: Re: [ADMIN] Migrating from 8.3 to 8.4 on the same server

Date: Wed, 14 Apr 2010 16:36:11 -0400

The database is about 12gb, the dump file is about 8gb.



I tested dump/restore into 8.4 on our test server, and it took an hour. 

  This is a virtualized server, but my sysadmin thinks the performance 

on our live server (not virtual) would be comparable.



It wouldn't kill us to have two or three hours of down time, but I would 

like to avoid it.



Thank you,



Lewis Kapell

Computer Operations

Seton Home Study School







On 4/14/2010 4:28 PM, Iñigo Martinez Lasala wrote:

> How big is your database?

>

> If not very big, a pg_dump/pg_restore will be your best option. 8.3 to

> 8.4 is not a traumatic upgrade. In fact, it's really easy and probably

> you won't need to change your database schema.

> And pg_restore in 8.4 is really FAST (compared with previous versions).

Re: Migrating from 8.3 to 8.4 on the same server

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> It wouldn't kill us to have two or three hours of down time,
> but I would like to avoid it.

If it wouldn't kill you, I'd suck up the time. All other solutions
are going to take additional expertise, prep, and testing.

Keep in mind that if you have tables and/or databases that don't
change frequently, you can dump those beforehand to minimize
the final downtime.

You can also put your db in readonly mode to keep it available
while the export is going on. Depending on your server layout,
(e.g. available partitions) this may or may not be worth it.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004141705
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvGL0UACgkQvJuQZxSWSshrFgCgxQJGmwW/GEHDysaYqBzW D2PM
KiUAoMaq/nMeQWV3hbE8uoQzoF/FZVlx
=3b+F
-----END PGP SIGNATURE-----



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

On 4/14/2010 5:11 PM, Greg Sabino Mullane wrote:
> If it wouldn't kill you, I'd suck up the time. All other solutions
> are going to take additional expertise, prep, and testing.
>
> Keep in mind that if you have tables and/or databases that don't
> change frequently, you can dump those beforehand to minimize
> the final downtime.

That's true, I had thought about that also. I guess I would save the
contents of the biggest tables to flat files using the COPY command,
then delete the copied rows, before doing the dump. Is what what you
had in mind?

Lewis


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> Keep in mind that if you have tables and/or databases that don't
>> change frequently, you can dump those beforehand to minimize
>> the final downtime.

> That's true, I had thought about that also. I guess I would save the
> contents of the biggest tables to flat files using the COPY command,
> then delete the copied rows, before doing the dump. Is what what you
> had in mind?

No need to delete the copied rows: just use the handy -t and -T flags
for pg_dump to include/exclude specific tables. You also don't need
to save to a flat file first unless you need/want a separate backup.
For large tables foo and bar, assuming 8.3 on port 5432 and 8.4 on port
5840, you could do:

* pg_dump -t foo -t bar -p 5432 | psql -p 5840 -f -

Then your final migration steps would be:

* Stop the app
* pg_dump -T foo -T bar -p 5432 | psql -p 5840 -f -
* Turn fsync back on and change the port to 5432 for 8.4
* Stop the 8.3 database
* Restart 8.4
* Start the app

All off the top of my head, requires heavy testing by QA first,
assumes foo and bar are not used, etc. /disclaimer

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004151050
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvHKDkACgkQvJuQZxSWSsjasACgmepXTrkz/rsoHsm/qKWx syB9
YlYAoJHHeyd768IJC5RqNn2aXPD/80fp
=6yRN
-----END PGP SIGNATURE-----



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin